package gwtappcontainer.server.apps.insight;
import gwtappcontainer.server.Limits;
import gwtappcontainer.server.Utils;
import gwtappcontainer.server.apps.APIException;
import gwtappcontainer.server.apps.security.AccessController;
import gwtappcontainer.shared.apis.APIResponse.Status;
import gwtappcontainer.shared.apps.insight.Center;
import gwtappcontainer.shared.apps.insight.Member;
import gwtappcontainer.shared.apps.insight.Member.ContactDetails;
import gwtappcontainer.shared.apps.insight.SyntaxChecker;
import gwtappcontainer.shared.apps.insight.UnlistedProgram;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.Set;
import com.google.gwt.dev.util.collect.HashMap;
import com.google.gwt.dev.util.collect.HashSet;
class MemberRepository {
public static Member get(String email, String login) {
return getByIdOrEmail(0, email, login);
}
public static Member get(int memberId, String login) {
return getByIdOrEmail(memberId, null, login);
}
private static Member getByIdOrEmail(int memberId, String email, String login) {
if ((memberId == 0) && (email == null))
throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED,
"Either id or email has to be specified");
String sql;
if (memberId == 0)
sql = "select member_id, first_name, middle_name, last_name, email, " +
"phone, home_address, office_address, home_phone, office_phone, singapore_nric " +
"from members, centers where email = ?";
else {
sql = "select member_id, first_name, middle_name, last_name, email, " +
"phone, home_address, office_address, home_phone, office_phone, singapore_nric " +
"from members, centers where member_id = ?";
}
Member member = null;
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
if (memberId == 0)
ps.setString(1, email.toLowerCase());
else {
ps.setInt(1, memberId);
}
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
member = new Member();
member.id = resultSet.getInt("member_id");
member.contactDetails.firstName = resultSet.getString("first_name");
member.contactDetails.lastName = resultSet.getString("last_name");
member.contactDetails.middleName = resultSet.getString("middle_name");
member.contactDetails.email = resultSet.getString("email");
member.contactDetails.phone = resultSet.getString("phone");
member.contactDetails.homeAddress = resultSet.getString("home_address");
member.contactDetails.officeAddress = resultSet.getString("office_address");
member.contactDetails.homePhone = resultSet.getString("home_phone");
member.contactDetails.officePhone = resultSet.getString("office_phone");
member.contactDetails.singaporeNRIC = resultSet.getString("singapore_nric");
}
}
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
if (null == member)
return null;
if (! member.contactDetails.email.equals(login))
AccessController.ensureValidUser(login);
sql = "select mc.center_id, center, owning_center from member_centers mc, centers c where " +
"member_id = ? and mc.center_id = c.center_id order by center";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setLong(1, member.id);
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
Center center = new Center();
center.id = resultSet.getInt(1);
center.name = resultSet.getString(2);
boolean owningCenter = resultSet.getBoolean(3);
if (owningCenter)
member.owningCenter = center;
else
member.additionalCenters.add(center);
}
}
}
sql = "select unlisted_program_id, up.program_type_id, pt.program_type, month, year, teacher, venue " +
"from unlisted_programs up, program_types pt where " +
"member_id = ? and up.program_type_id = pt.program_type_id order by (year + month)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setInt(1, member.id);
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
UnlistedProgram unlistedProgram = new UnlistedProgram();
unlistedProgram.id = resultSet.getInt(1);
unlistedProgram.programTypeId = resultSet.getInt(2);
unlistedProgram.programType = resultSet.getString(3);
unlistedProgram.month = resultSet.getInt(4);
unlistedProgram.year = resultSet.getInt(5);
unlistedProgram.teacher = resultSet.getString(6);
unlistedProgram.venue = resultSet.getString(7);
member.unlistedPrograms.add(unlistedProgram);
}
}
}
return member;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static void add(ContactDetails contactDetails, int owningCenterId, String login) {
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
SyntaxChecker.ensureValid(contactDetails);
Validator.ensureValidCenter(owningCenterId);
contactDetails.email = contactDetails.email.toLowerCase();
if (null != get(contactDetails.email, login))
throw new APIException(Status.ERROR_RESOURCE_ALREADY_EXISTS,
"Member [" + contactDetails.email + "] already exists");
try {
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
connection.setAutoCommit(false);
String sql = "insert into members values (0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, contactDetails.firstName);
ps.setString(2, contactDetails.middleName);
ps.setString(3, contactDetails.lastName);
ps.setString(4, contactDetails.email);
ps.setString(5, contactDetails.phone);
ps.setString(6, contactDetails.homeAddress);
ps.setString(7, contactDetails.officeAddress);
ps.setString(8, contactDetails.homePhone);
ps.setString(9, contactDetails.officePhone);
ps.setString(10, contactDetails.singaporeNRIC);
ps.executeUpdate();
}
sql = "select member_id from members where email = ?";
int memberId = 0;
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, contactDetails.email);
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
memberId = resultSet.getInt(1);
}
}
}
sql = "insert into member_centers values (?, ?, ?, ?)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setInt(1, memberId);
ps.setInt(2, owningCenterId);
ps.setBoolean(3, true);
ps.setBoolean(4, true);
ps.executeUpdate();
}
connection.commit();
connection.setAutoCommit(true);
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
static void addMany(List<ContactDetails> contactDetails, int owningCenterId,
String login) {
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
SyntaxChecker.ensureValid(contactDetails);
List<String> emails = new ArrayList<>();
for (ContactDetails contact : contactDetails)
emails.add(contact.email);
String sql = "insert into members (member_id, first_name, middle_name, " +
"last_name, email, phone, home_address, office_address, home_phone, " +
"office_phone, singapore_nric) values (0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
for (ContactDetails contact : contactDetails) {
ps.setString(1, contact.firstName);
ps.setString(2, contact.middleName);
ps.setString(3, contact.lastName);
ps.setString(4, contact.email.toLowerCase());
ps.setString(5, contact.phone);
ps.setString(6, contact.homeAddress);
ps.setString(7, contact.officeAddress);
ps.setString(8, contact.homePhone);
ps.setString(9, contact.officePhone);
ps.setString(10, contact.singaporeNRIC);
ps.addBatch();
}
connection.setAutoCommit(false);
ps.executeBatch();
}
List<Member> members = getMemberIdAndContactDetails(emails, connection);
sql = "insert into member_centers (member_id, center_id, receive_email, owning_center) " +
"values (?, ?, ?, ?)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
for (Member member : members) {
ps.setInt(1, member.id);
ps.setInt(2, owningCenterId);
ps.setBoolean(3, true);
ps.setBoolean(4, true);
ps.addBatch();
}
ps.executeBatch();
connection.commit();
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static void updateContactDetails(int memberId, Member.ContactDetails contactDetails, String login) {
String email = getEmailFromMemberId(memberId);
if (email == null)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"There is no member with id [" + memberId + "]");
if (! login.equals(email))
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
SyntaxChecker.ensureValid(contactDetails);
String sql = "update members set " +
"first_name = ?, middle_name = ?, " +
"last_name = ?, email = ?, phone = ?, home_address = ?, " +
"office_address = ?, home_phone = ?, office_phone = ?, " +
"singapore_nric = ? where member_id = ?";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, contactDetails.firstName);
ps.setString(2, contactDetails.middleName);
ps.setString(3, contactDetails.lastName);
ps.setString(4, contactDetails.email.toLowerCase());
ps.setString(5, contactDetails.phone);
ps.setString(6, contactDetails.homeAddress);
ps.setString(7, contactDetails.officeAddress);
ps.setString(8, contactDetails.homePhone);
ps.setString(9, contactDetails.officePhone);
ps.setString(10, contactDetails.singaporeNRIC);
ps.setLong(11, memberId);
ps.executeUpdate();
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static void updateManyContactDetails(List<Integer> memberIds, List<ContactDetails> contactDetails,
String login) {
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
SyntaxChecker.ensureValid(contactDetails);
if (memberIds.size() != contactDetails.size())
throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED,
"List size mismatch. Num of members ids [" + memberIds.size() +
"] does not match no of contact details [" + contactDetails.size() + "]");
String sql = "update members set " +
"first_name = ?, middle_name = ?, " +
"last_name = ?, email = ?, phone = ?, home_address = ?, " +
"office_address = ?, home_phone = ?, office_phone = ?, " +
"singapore_nric = ? where member_id = ?";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
for (int i = 0; i < memberIds.size(); i++) {
ps.setString(1, contactDetails.get(i).firstName);
ps.setString(2, contactDetails.get(i).middleName);
ps.setString(3, contactDetails.get(i).lastName);
ps.setString(4, contactDetails.get(i).email.toLowerCase());
ps.setString(5, contactDetails.get(i).phone);
ps.setString(6, contactDetails.get(i).homeAddress);
ps.setString(7, contactDetails.get(i).officeAddress);
ps.setString(8, contactDetails.get(i).homePhone);
ps.setString(9, contactDetails.get(i).officePhone);
ps.setString(10, contactDetails.get(i).singaporeNRIC);
ps.setLong(11, memberIds.get(i));
ps.addBatch();
}
ps.executeBatch();
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static void addCenter(long memberId, long centerId, String login) {
String email = getEmailFromMemberId(memberId);
if (null == email)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"There is no member with id [" + memberId + "]");
if (! email.equals(login))
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
try {
String sql = "insert into member_centers (member_id, center_id, receive_email, owning_center) values (?, ?, ?, ?)";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setLong(1, memberId);
ps.setLong(2, centerId);
ps.setBoolean(3, true);
ps.setBoolean(4, false);
ps.executeUpdate();
}
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static void deleteCenter(int memberId, int centerId, String login) {
Member member = get(memberId, login);
if (null == member)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"There is no member with id [" + memberId + "]");
if (! login.equals(member.contactDetails.email))
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
if (centerId == member.owningCenter.id)
throw new APIException(Status.ERROR_PRECONDITION_FAILURE,
"Cannot delete owning center [" + centerId + "] for member [" + memberId + "]");
try {
String sql = "delete from member_centers where member_id = ? and center_id = ?";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setLong(1, memberId);
ps.setLong(2, centerId);
ps.executeUpdate();
}
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static void addUnlistedProgram(long memberId, UnlistedProgram unlistedProgram, String login) {
if (unlistedProgram.month < 1 || unlistedProgram.month > 12)
throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED,
"Month should be between 1 and 12");
if (unlistedProgram.year < 1970 || unlistedProgram.year > Calendar.getInstance().get(Calendar.YEAR))
throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED,
"Year should be after 1970 and lesser than or equal to current year");
String email = getEmailFromMemberId(memberId);
if (null == email)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"There is no member with id [" + memberId + "]");
if (! email.equals(login))
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
try {
String sql = "insert into unlisted_programs values (0, ?, ?, ?, ?, ?, ?)";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setInt(1, unlistedProgram.programTypeId);
ps.setInt(2, unlistedProgram.month);
ps.setInt(3, unlistedProgram.year);
ps.setString(4, unlistedProgram.teacher);
ps.setString(5, unlistedProgram.venue);
ps.setLong(6, memberId);
ps.executeUpdate();
}
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static void deleteUnlistedProgram(int unlistedProgramId, String login) {
String email = getEmailFromUnlistedProgramId(unlistedProgramId, login);
if (null == email)
throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
"There is no unlisted program with id [" + unlistedProgramId + "]");
if (! email.equals(login))
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
try {
String sql = "delete from unlisted_programs where unlisted_program_id = ?";
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setInt(1, unlistedProgramId);
ps.executeUpdate();
}
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
public static String addOrUpdateMany(int owningCenterId, List<ContactDetails> contactDetails,
String login) {
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
if (contactDetails.size() > Limits.Member.MAX_NUMRECORDS_BULKIMPORT)
throw new APIException(Status.ERROR_OUT_OF_BOUNDS,
"No of records [" + contactDetails.size() + "] is greater than max allowed [" +
Limits.Member.MAX_NUMRECORDS_BULKIMPORT + "]");
SyntaxChecker.ensureValid(contactDetails);
List<String> emails = new ArrayList<>();
Set<String> emailSet = new HashSet<>();
for (ContactDetails contact : contactDetails) {
contact.email = contact.email.toLowerCase(); //email always in lower case
if (emailSet.contains(contact.email))
throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED,
"Email [" + contact.email + "] is a duplicate");
emails.add(contact.email);
emailSet.add(contact.email);
}
List<Member> existing = getMemberIdAndContactDetails(emails, login);
Map<String, Member> existingMap = new HashMap<>();
for (Member member : existing)
existingMap.put(member.contactDetails.email, member);
List<ContactDetails> membersToBeUpdated = new ArrayList<>();
List<ContactDetails> membersToBeInserted = new ArrayList<>();
List<Integer> memberIds = new ArrayList<>();
for (ContactDetails contact : contactDetails) {
if (existingMap.containsKey(contact.email)) {
Member member = existingMap.get(contact.email);
membersToBeUpdated.add(applyChanges(member.contactDetails, contact));
memberIds.add(member.id);
}
else
membersToBeInserted.add(contact);
}
MemberRepository.addMany(membersToBeInserted, owningCenterId, login);
MemberRepository.updateManyContactDetails(memberIds, membersToBeUpdated, login);
return "Inserted [" + membersToBeInserted.size() + "] records and updated [" +
membersToBeUpdated.size() + "] records";
}
static ContactDetails applyChanges(final ContactDetails contactDetails, ContactDetails changes) {
if ((changes.email != null) && (! changes.email.equals("")))
contactDetails.email = changes.email;
if ((changes.phone != null) && (! changes.phone.equals("")))
contactDetails.phone = changes.phone;
if ((changes.firstName != null) && (! changes.firstName.equals("")))
contactDetails.firstName = changes.firstName;
if ((changes.middleName != null) && (! changes.middleName.equals("")))
contactDetails.middleName = changes.middleName;
if ((changes.lastName != null) && (! changes.lastName.equals("")))
contactDetails.lastName = changes.lastName;
if ((changes.homeAddress != null) && (! changes.homeAddress.equals("")))
contactDetails.homeAddress = changes.homeAddress;
if ((changes.homePhone != null) && (! changes.homePhone.equals("")))
contactDetails.homePhone = changes.homePhone;
if ((changes.officeAddress != null) && (! changes.officeAddress.equals("")))
contactDetails.officeAddress = changes.officeAddress;
if ((changes.officePhone != null) && (! changes.officePhone.equals("")))
contactDetails.officePhone = changes.officePhone;
if ((changes.singaporeNRIC != null) && (! changes.singaporeNRIC.equals("")))
contactDetails.singaporeNRIC = changes.singaporeNRIC;
return contactDetails;
}
static List<Member> getMemberIdAndContactDetails(List<String> emails, String login) {
AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
return getMemberIdAndContactDetails(emails, connection);
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
private static List<Member> getMemberIdAndContactDetails(List<String> emails,
Connection connection) throws SQLException {
List<Member> members = new ArrayList<Member>();
if (emails.size() == 0)
return members;
String sql = "select member_id, first_name, middle_name, last_name, email, " +
"phone, home_address, office_address, home_phone, office_phone, singapore_nric " +
"from members where email in (";
String questionMarks = "?";
for (int i = 1; i < emails.size(); i++) {
questionMarks += ", ?";
}
sql += questionMarks + ")";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
for (int i = 0; i < emails.size(); i++)
ps.setString(i + 1, emails.get(i));
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
Member member = new Member();
member.id = resultSet.getInt(1);
member.contactDetails.firstName = resultSet.getString(2);
member.contactDetails.middleName = resultSet.getString(3);
member.contactDetails.lastName = resultSet.getString(4);
member.contactDetails.email = resultSet.getString(5);
member.contactDetails.phone = resultSet.getString(6);
member.contactDetails.homeAddress = resultSet.getString(7);
member.contactDetails.officeAddress = resultSet.getString(8);
member.contactDetails.homePhone = resultSet.getString(9);
member.contactDetails.officePhone = resultSet.getString(10);
member.contactDetails.singaporeNRIC = resultSet.getString(11);
members.add(member);
}
}
return members;
}
}
private static String getEmailFromMemberId(long memberId) {
try {
String sql = "select email from members where member_id = ?";
String email = null;
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setLong(1, memberId);
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
email = resultSet.getString(1);
}
}
}
}
return email;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
private static String getEmailFromUnlistedProgramId(long unlistedProgramId, String login) {
try {
String sql = "select email from members where member_id = (select member_id from unlisted_programs where unlisted_program_id = ?)";
String email = null;
try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setLong(1, unlistedProgramId);
try (ResultSet resultSet = ps.executeQuery()) {
while (resultSet.next()) {
email = resultSet.getString(1);
}
}
}
}
return email;
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
}